﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Liger.Data;
using Liger.Model;
using Liger.Data.Linq;

namespace Test
{
    public class NorthwindWhereTest : BaseTest
    {
        public NorthwindWhereTest(DbContext db)
            : base(db)
        {
        }

        public override bool MethodEnabled(string name)
        {
            //return name == "TestFildISNULL";
            return true;
        }

        /// <summary>
        /// 测试 is null
        /// </summary>
        public void TestFildISNULL()
        {

            var result = Db.From<Customers>().Where(Customers._.CustomerID == null).Count();


            this.AssertReturn(result == 0);
        }

        /// <summary>
        /// 测试 is not null
        /// </summary>
        public void TestFildISNotNULL()
        {
            var result = Db.From<Customers>().Where(Customers._.CustomerID != null).Count();


            this.AssertReturn(result == 0);
        }


        /// <summary>
        /// 测试 字段和字段的对比
        /// </summary>
        public void TestFildAndField()
        {
           var result =  Db.From<Customers>().Where(Customers._.CustomerID == Customers._.CompanyName).Count();


           this.AssertReturn(result == 0);
        }


        /// <summary>
        /// 测试 字段和值的对比(字符串)
        /// </summary>
        public void TestCustomersEqualToALFKI()
        {
            var result = Db.From<Customers>().Where(Customers._.CustomerID == "ALFKI").Count();


            this.AssertReturn(result == 1);
        }

        /// <summary>
        /// 测试 字段和值的对比(Int)
        /// </summary>
        public void TestOrderIDEqual()
        {
            var result = Db.From<Orders>().Where(Orders._.OrderID == 10248).Count();


            this.AssertReturn(result == 1);
        }

        /// <summary>
        /// 测试 字段和值的对比(Int)(大于)
        /// </summary>
        public void TestOrderIDGreater()
        {
            var result = Db.From<Orders>().Where(Orders._.OrderID >= 10248).Count();

            this.AssertReturn(result == 830);
        }

        /// <summary>
        /// 测试时间
        /// </summary>
        public void TestDateTimeGreater()
        {
            var result = Db.From<Orders>()
                .Select(Orders._.OrderDate)
                .Where(Orders._.OrderDate >= new DateTime(1994, 1, 1))
                .ToScalar<DateTime>();

            this.AssertReturn(result >= new DateTime(1994, 1, 1));
        }


        /// <summary>
        /// 测试时间。两个时间中间
        /// 订单时间在 1995/10/01 - 1999/11/01 之间
        /// </summary>
        public void TestDateTimeBetween()
        {
            var result = Db.From<Orders>()
                .Select(Orders._.OrderDate)
                .Where(Orders._.OrderDate >= new DateTime(1995, 10, 1) && Orders._.OrderDate <= new DateTime(1999, 11, 1))
                .ToScalar<DateTime>();

            this.AssertReturn(result >= new DateTime(1995, 10, 1));
        }



        /// <summary>
        /// 测试浮点数值 
        /// 运费 在 40 - 50 之间
        /// </summary>
        public void TestFreightBetween()
        {
            var result = Db.From<Orders>()
                .Select(Orders._.Freight)
                .Where(Orders._.Freight >= 40 && Orders._.Freight <= 50)
                .ToScalar<decimal>();

            this.AssertReturn(result >= 40 && result <= 50);
        }



        /// <summary>
        /// 测试and where 
        /// </summary>
        public void TestWhereAnd()
        {
            var result = Db.From<Orders>() 
                .Where(Orders._.Freight >= 40 && Orders._.OrderDate >= "1994-10-01")
                .ToFirst();

            this.AssertReturn(result != null);
        }

        /// <summary>
        /// 测试and where 三个where
        /// </summary>
        public void TestWhereAnd2()
        {
            var result = Db.From<Orders>()
                .Where(Orders._.Freight >= 40 && Orders._.OrderDate >= "1994-10-01" && Orders._.Freight <= 50)
                .ToFirst();

            this.AssertReturn(result != null);
        }


        /// <summary>
        /// 测试or where 
        /// </summary>
        public void TestWhereOr()
        {
            var result = Db.From<Orders>()
                .Where(Orders._.Freight >= 80 || Orders._.OrderDate >= "1997-01-01")
                .ToFirst();

            this.AssertReturn(result != null);
        }

        /// <summary>
        /// 测试or where 三个where
        /// </summary>
        public void TestWhereOr2()
        {
            var result = Db.From<Orders>()
                .Where(Orders._.Freight >= 80 || Orders._.OrderDate >= "1997-01-01" || Orders._.EmployeeID == 9)
                .ToFirst();

            this.AssertReturn(result != null);
        }


        /// <summary>
        /// 测试or 与 and 同时存在 where 三个where
        /// </summary>
        public void TestWhereOrAnd()
        {
            var result = Db.From<Orders>()
                .Where(Orders._.Freight >= 90 || Orders._.OrderDate >= "1998-01-01" && Orders._.EmployeeID == 1)
                .ToFirst();

            this.AssertReturn(result != null);
        }

        /// <summary>
        /// 测试 Between
        /// </summary>
        public void TestBetween()
        {
            var result = Db.From<Orders>().Where(Orders._.Freight.Between(40,50)).Count();

            this.AssertReturn(result == 59);
        }


        /// <summary>
        /// 测试 like
        /// </summary>
        public void TestFieldWhereLike()
        {
            var ShipName = Db.From<Orders>()
                .Select(Orders._.ShipName)
                .Where(Orders._.ShipName.Like("Carnes"))
                .ToScalar<string>();

            this.AssertReturn(ShipName == "Hanari Carnes");
        }


        /// <summary>
        /// 测试 StartWith
        /// </summary>
        public void TestFieldWhereStartWith()
        {
            var ShipName = Db.From<Orders>()
               .Select(Orders._.ShipName)
               .Where(Orders._.ShipName.StartsWith("Hanari"))
               .ToScalar<string>();
            this.AssertReturn(ShipName == "Hanari Carnes");
        }


        /// <summary>
        /// 测试 EndWith
        /// </summary>
        public void TestFieldWhereEndWith()
        {
            var ShipName = Db.From<Orders>()
              .Select(Orders._.ShipName)
              .Where(Orders._.ShipName.EndsWith("Carnes"))
              .ToScalar<string>();
            this.AssertReturn(ShipName == "Hanari Carnes");
        }

        /// <summary>
        /// 测试 linq where(来自MS自带例子)
        /// 此示例使用 WHERE 筛选在伦敦的客户。
        /// </summary>
        public void TestLinqToSqlWhere01()
        {
            var q =
                from c in Db.Query<Customers>()
                where c.City == "London"
                select c;
            this.AssertReturn(q.ToList() != null);
        }

        /// <summary>
        /// 测试 linq where(来自MS自带例子)
        /// 此示例使用 WHERE 筛选1994 年或之后雇用的雇员。
        /// </summary>
        public void TestLinqToSqlWhere02()
        {
            var q =
                from e in Db.Query<Employees>()
                where e.HireDate >= new DateTime(1994, 1, 1)
                select e;

            this.AssertReturn(q.ToList() != null);
        }
 
    }
}
